package com.huabo.system.oracle.mapper;

import java.math.BigDecimal;
import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.annotations.UpdateProvider;

import com.hbfk.util.PageInfo;
import com.huabo.system.oracle.entity.Find;
import com.huabo.system.oracle.entity.TblManageRight;
import com.huabo.system.oracle.entity.TblOrganization;
import com.huabo.system.oracle.entity.TblRole;
import com.huabo.system.oracle.entity.TblStaff;
import com.huabo.system.service.UserService;

import io.lettuce.core.dynamic.annotation.Param;
import net.sf.json.JSONObject;
import tk.mybatis.mapper.common.BaseMapper;

public interface TblStaffMapper extends BaseMapper<TblStaff> {

	@Select("SELECT STAFFID,REALNAME,ORGID,USERNAME FROM TBL_STAFF WHERE STAFFID = #{staffId}")
	@Results({
		@Result(column="STAFFID",property="staffid"),
		@Result(column="REALNAME",property="realname"),
		@Result(column="ORGID",property="orgid"),
		@Result(column="USERNAME",property="username"),
	})
	TblStaff selectStaff(@Param("staffId")String staffId) throws Exception;
	
	@Select("SELECT TS.STAFFID,TS.REALNAME,TS.USERNAME,TS.ADDRESS,TS.PKYMSTAFFID,TS.MIBLEPHONE,TS.MEMO,TS.STATUS,TS.CREATETIME,TS.JOBID,TS.OUTSIDEID,TS.OUTSIDEOPENDID,TS.EMAIL,TS.FIXEDPHONE,TR.RID,TR.RNAME,ORG.ORGID,ORG.ORGNAME,TS.ROLEIDSTRS,(SELECT WM_CONCAT(RNAME) FROM TBL_ROLE WHERE INSTR(','||TS.ROLEIDSTRS||',',','||RID||',')>0) RNAMES FROM TBL_STAFF TS LEFT JOIN TBL_ROLE TR ON TS.ROLEID = TR.RID LEFT JOIN TBL_ORGANIZATION ORG ON TS.ORGID = ORG.ORGID WHERE TS.USERNAME = #{userName} AND TS.PASSWORD = #{password}")
	@Results({
		@Result(column="STAFFID",property="staffid"),
		@Result(column="REALNAME",property="realname"),
		@Result(column="USERNAME",property="username"),
		@Result(column="ADDRESS",property="address"),
		@Result(column="EMAIL",property="email"),
		@Result(column="PKYMSTAFFID",property="pkYmStaffId"),
		@Result(column="MIBLEPHONE",property="miblephone"),
		@Result(column="MEMO",property="memo"),
		@Result(column="STATUS",property="status"),
		@Result(column="CREATETIME",property="createDate"),
		@Result(column="JOBID",property="jobid"),
		@Result(column="OUTSIDEID",property="outSideId"),
		@Result(column="OUTSIDEOPENDID",property="outSideOpenId"),
		@Result(column="FIXEDPHONE",property="fixedphone"),
		@Result(column="RID",property="trole.rid",id=true),
		@Result(column="RNAME",property="trole.rname"),
		@Result(column="ORGID",property="linkDetp.orgid",id=true),
		@Result(column="ORGNAME",property="linkDetp.orgname"),
		@Result(column="ROLEIDSTRS",property="roleIdStrs"),
		@Result(column="RNAMES",property="roleNames"),
	})
	TblStaff selectUniqueStaffInfo(@Param("userName")String userName,@Param("password") String password) throws Exception;

	@Select("SELECT TS.STAFFID,TS.REALNAME,TS.USERNAME,TS.ADDRESS,TS.MIBLEPHONE,TS.MEMO,TS.STATUS,TS.CREATETIME,TS.JOBID,TS.OUTSIDEID,TS.OUTSIDEOPENDID,TS.EMAIL,TS.FIXEDPHONE,TR.RID,TR.RNAME,ORG.ORGID,ORG.ORGNAME,TS.ROLEIDSTRS,(SELECT WM_CONCAT(RNAME) FROM TBL_ROLE WHERE INSTR(TS.ROLEIDSTRS,RID)>0) RNAMES FROM TBL_STAFF TS LEFT JOIN TBL_ROLE TR ON TS.ROLEID = TR.RID LEFT JOIN TBL_ORGANIZATION ORG ON TS.ORGID = ORG.ORGID WHERE TS.USERNAME = #{userName}")
	@Results({
		@Result(column="STAFFID",property="staffid"),
		@Result(column="REALNAME",property="realname"),
		@Result(column="USERNAME",property="username"),
		@Result(column="ADDRESS",property="address"),
		@Result(column="EMAIL",property="email"),
		@Result(column="MIBLEPHONE",property="miblephone"),
		@Result(column="MEMO",property="memo"),
		@Result(column="STATUS",property="status"),
		@Result(column="CREATETIME",property="createDate"),
		@Result(column="JOBID",property="jobid"),
		@Result(column="OUTSIDEID",property="outSideId"),
		@Result(column="OUTSIDEOPENDID",property="outSideOpenId"),
		@Result(column="FIXEDPHONE",property="fixedphone"),
		@Result(column="RID",property="trole.rid",id=true),
		@Result(column="RNAME",property="trole.rname"),
		@Result(column="ORGID",property="linkDetp.orgid",id=true),
		@Result(column="ORGNAME",property="linkDetp.orgname"),
		@Result(column="ROLEIDSTRS",property="roleIdStrs"),
		@Result(column="RNAMES",property="roleNames"),
	})
	TblStaff selectUniqueStaffInfoByname(@Param("userName")String userName) throws Exception;

	@Select("select count(*) from TBL_STAFF where ORGID = #{ORGID} and STATUS = 1")
	boolean selectFindChildrenByOrgid(List<TblOrganization> findChildrenByOrgid);


	@Select("select S.*,O.ORGNAME from TBL_STAFF S LEFT JOIN TBL_ORGANIZATION O ON S.ORGID = O.ORGID WHERE S.STAFFID = #{id}")
    TblStaff findById(BigDecimal id);

	@Update("UPDATE TBL_STAFF SET PASSWORD = #{password} WHERE USERNAME =#{username}")
	void attachDirty(TblStaff username);

	@Select("SELECT s.STAFFID,s.REALNAME,o.ORGNAME FROM TBL_STAFF s LEFT JOIN TBL_ORGANIZATION o ON s.ORGID = o.ORGID WHERE s.STAFFID IN (select STAFFID from TBL_MANAGE_USER_BOOK WHERE BOOKID IN (%s)) AND s.ORGID IN (SELECT ORGID FROM TBL_ORGANIZATION where ORGTYPE = 0 start with ORGID = %s connect by prior ORGID = FATHERORGID)")
    List<TblStaff> selectDan();

	@Select("SELECT count(*) FROM TBL_STAFF s LEFT JOIN TBL_ORGANIZATION o ON s.ORGID = o.ORGID WHERE s.STAFFID IN (select STAFFID from TBL_MANAGE_USER_BOOK WHERE BOOKID IN (%s)) AND s.ORGID IN (SELECT ORGID FROM TBL_ORGANIZATION where ORGTYPE = 0 start with ORGID = %s connect by prior ORGID = FATHERORGID)")
	List<TblStaff> selectCountt();

	@Select("SELECT * FROM TBL_STAFF WHERE STAFFID =#{pid}")
    TblStaff selectUserId(BigDecimal pid);

	@SelectProvider(method = "selectListByPageInfoo",type =TblStaffMapperSqlConifg.class )
    List<TblStaff> selectListByPageInfoo(PageInfo<TblStaff> pageInfo, Find find,BigDecimal orgid);
	
	@SelectProvider(method = "selectListByPageInfo",type = TblStaffMapperSqlConifg.class)
	List<UserService> selectListByPageInfo(PageInfo<UserService> pageInfo);

    @SelectProvider(method = "selectListByPageIn",type = TblStaffMapperSqlConifg.class)
	List<TblStaff> selectListByPageIn(PageInfo<TblStaff> pageInfo,BigDecimal pid,Find find);

	@SelectProvider(method = "selectListByPageInfoOrgid",type =TblStaffMapperSqlConifg.class )
	List<TblStaff> selectListByPageInfoOrgid(PageInfo<TblStaff> pageInfo, BigDecimal pid,Find find);


	@Select("select * from TBL_STAFF WHERE USERNAME = #{username}")
    TblStaff selectUserName(String username);

//	@Insert("INSERT INTO TBL_STAFF (REALNAME,STATUS,FIXEDPHONE,MIBLEPHONE,ADDRESS,EMAIL,MEMO,USERNAME,PASSWORD) " +
//			"VALUES(#{realname},#{status},#{fixedphone},#{miblephone},#{address},#{email},#{memo},#{username},#{password})")
	@InsertProvider(method = "insertUser",type = TblStaffMapperSqlConifg.class)
	@Options(useGeneratedKeys=true, keyProperty="staffid", keyColumn="STAFFID")
	void insertUser(TblStaff user);

	@Select("select * from TBL_STAFF WHERE EMAIL = #{email}")
    List<TblStaff> selectEmail(String email);

		@Update("UPDATE TBL_STAFF SET PASSWORD =#{password},PASSWORD = #{possword1} WHERE STAFFID = #{pid}")
	//@UpdateProvider(type=TblStaffMapperSqlConifg.class,method="updateUser")
	void updateUser(TblStaff user);

	@Select("SELECT TS.STAFFID,TS.REALNAME,TS.ADDRESS,TS.EMAIL,TS.MIBLEPHONE,TS.MEMO,TS.USERNAME,TS.PASSWORD,TS.STATUS,TS.CREATETIME,TS.JOBID,TS.ROLEIDSTRS,(SELECT WM_CONCAT(RNAME) FROM TBL_ROLE WHERE INSTR(TS.ROLEIDSTRS,RID)>0) RNAMES,TOR.ORGID,TOR.ORGNAME FROM TBL_STAFF TS LEFT JOIN TBL_ORGANIZATION TOR ON TS.ORGID = TOR.ORGID WHERE TS.STAFFID = #{id}")
	@Results({
		@Result(column="STAFFID",property="staffid"),
		@Result(column="REALNAME",property="realname"),
		@Result(column="ADDRESS",property="address"),
		@Result(column="EMAIL",property="email"),
		@Result(column="MIBLEPHONE",property="miblephone"),
		@Result(column="MEMO",property="memo"),
		@Result(column="ORGID",property="orgid"),
		@Result(column="USERNAME",property="username"),
		@Result(column="PASSWORD",property="password"),
		@Result(column="STATUS",property="status"),
		@Result(column="CREATETIME",property="createDate"),
		@Result(column="JOBID",property="jobid"),
		@Result(column="ROLEIDSTRS",property="roleIdStrs"),
		@Result(column="RNAMES",property="roleNames"),
		@Result(column="ORGNAME",property="orgname"),
	})
	TblStaff selectPid(String id);
	
	
//	@SelectProvider(method = "selectCountFind",type =TblStaffMapperSqlConifg.class )
	@Select("select count(*) from tbl_staff where orgid= #{orgid} order by STATUS desc,STAFFID desc")
	Integer selectCountFind(PageInfo<TblStaff> pageInfo, BigDecimal orgid);


//	@SelectProvider(method = "selectListByPageInfoCount",type =TblStaffMapperSqlConifg.class )
	@Select("select COUNT(*) from TBL_STAFF TS LEFT JOIN TBL_ORGANIZATION TOR ON TS.ORGID = TOR.ORGID WHERE TS.ORGID =#{pid}")
	Integer selectListByPageInfoCount(BigDecimal pid);

//	@SelectProvider(method = "selectListByPageInfoCountOrgid",type = TblStaffMapperSqlConifg.class)
	@Select("select COUNT(*) from TBL_STAFF TS LEFT JOIN TBL_ORGANIZATION TOR ON TS.ORGID = TOR.ORGID WHERE TOR.FATHERORGID =  #{orgid} AND TOR.ORGTYPE = 0 ")
	Integer selectListByPageInfoCountOrgid(BigDecimal orgid);

//	@SelectProvider(method = "selectListByPageInCount",type = TblStaffMapperSqlConifg.class)
	@Select("select COUNT(*) from tbl_staff where ORGID in (select ORGID from TBL_ORGANIZATION org where FATHERORGID= #{pid} AND ORGTYPE=0 )")
	Integer selectListByPageInCount(PageInfo<TblStaff> pageInfo,BigDecimal pid);

	@SelectProvider(method = "selectListByPageInfoFind",type = TblStaffMapperSqlConifg.class)
	List<TblStaff> selectListByPageInfoFind(PageInfo<TblStaff> pageInfo, BigDecimal orgid, Find find);

	@Select("select * from TBL_STAFF where ORGID = #{orgid}")
    String selectFindOrgid(TblOrganization orgid);

	@Delete("DELETE FROM TBL_STAFF WHERE ORGID = #{orgid}")
	void deleteOrgid(TblOrganization orgid);

	@Select("SELECT staff.*,TOR.ORGNAME FROM TBL_STAFF staff"
			+ " LEFT JOIN TBL_ORGANIZATION TOR ON staff.ORGID = TOR.ORGID "
			+ "WHERE STAFFID = #{staffid}")
    TblStaff findByStaffid(BigDecimal staffid);


	@UpdateProvider(type=TblStaffMapperSqlConifg.class,method="updateStaff")
	void updateStaff(TblStaff user);

	@Select("SELECT * FROM TBL_STAFF WHERE STAFFID = #{userid}")
	TblStaff selectByUserId(String userid);

	@SelectProvider(method = "selectListByPage",type = TblStaffMapperSqlConifg.class)
	List<TblStaff> selectListByPage(PageInfo<TblStaff> pageInfo, String pid);

	@SelectProvider(method = "selectListByPageCount",type = TblStaffMapperSqlConifg.class)
	Integer selectListByPageCount(PageInfo<TblStaff> pageInfo, String pid);

	@SelectProvider(method = "selectListBy",type = TblStaffMapperSqlConifg.class)
    List<TblStaff> selectListBy(PageInfo<TblStaff> pageInfo, BigDecimal orgid);

	@SelectProvider(method = "selectListByOrgid",type = TblStaffMapperSqlConifg.class)
	Integer selectListByOrgid(PageInfo<TblStaff> pageInfo, BigDecimal orgid);
	@SelectProvider(method = "findPageListBySql",type = TblStaffMapperSqlConifg.class)
    List<TblStaff> findPageListBySql(PageInfo<TblStaff> pageInfo);

	@SelectProvider(method = "findPageCountBySql",type = TblStaffMapperSqlConifg.class)
	Integer findPageCountBySql(PageInfo<TblStaff> pageInfo);

	@Select("SELECT * FROM TBL_STAFF where STAFFID = #{staffid}")
	TblStaff getExpert(BigDecimal staffid);

	@SelectProvider(method = "findAllPageInfoByacctid",type = TblStaffMapperSqlConifg.class)
    List<TblStaff> findAllPageInfoByacctid(PageInfo<TblStaff> pageInfo, String bookid);

	@SelectProvider(method = "findAllPageInfoByacctidcCount",type = TblStaffMapperSqlConifg.class)
	Integer findAllPageInfoByacctidcCount(String bookid);

	@SelectProvider(method = "findByAll",type = TblStaffMapperSqlConifg.class)
    List<TblStaff> findByAll(String pid, PageInfo<TblStaff> pageInfo);

	@Select(" select COUNT(*) from TBL_STAFF TS LEFT JOIN TBL_ORGANIZATION TOR ON TS.ORGID = TOR.ORGID WHERE TS.ORGID = #{pid} AND (TS.STATUS is NULL or TS.STATUS != 0)")
	Integer findByAllCount(String pid);

	@SelectProvider(method = "findByAllORGID",type = TblStaffMapperSqlConifg.class)
	List<TblStaff> findByAllORGID(BigDecimal orgid, PageInfo<TblStaff> pageInfo);

	@Select("SELECT COUNT(*) FROM TBL_STAFF TS LEFT JOIN TBL_ORGANIZATION TOR ON TS.ORGID = TOR.ORGID " +
			"WHERE TOR.FATHERORGID = #{orgid} AND (TS.STATUS IS NULL OR TS.STATUS != 0)")
	Integer findByAllORGIDCount(BigDecimal orgid);

	@SelectProvider(method = "findAllPageBeanPid",type = TblStaffMapperSqlConifg.class)
	List<TblStaff> findAllPageBeanPid(PageInfo<TblStaff> pageInfo, TblOrganization attribute);
	@SelectProvider(method = "findCountPageBeanPid",type = TblStaffMapperSqlConifg.class)
	Integer findCountPageBeanPid(PageInfo<TblStaff> pageInfo, TblOrganization attribute);

	@Select("select STA.STAFFID,ORA.ORGID,ORA.FATHERORGID,@ROW := @ROW + 1 AS SIGNED from(SELECT @ROW := 0) R,TBL_STAFF sta  INNER JOIN TBL_ORGANIZATION ora on STA.ORGID=ORA.ORGID  where  STA.STAFFID= #{cystaffid} HAVING SIGNED = 1")
	TblStaff findByOrag(String cystaffid);

	@Select("SELECT * FROM TBL_MANAGE_USER_RIGHT WHERE STAFFID = #{staffid}")
    List<TblManageRight> findMansgeUserRight(String staffid);

	@Select("SELECT * FROM TBL_STAFF WHERE JOBID = #{jobid}")
	List<TblStaff> findByJobid(BigDecimal jobid);

	@Select("SELECT PKYMSTAFFID FROM TBL_STAFF WHERE STAFFID = #{staffid}")
	TblStaff selectYmPkStaffIdByStaffId(BigDecimal staffid) throws Exception;

	@Results({
		@Result(column="STAFFID",property="staffid"),
		@Result(column="USERNAME",property="username"),
		@Result(column="EMAIL",property="email"),
		@Result(column="MIBLEPHONE",property="miblephone"),
		@Result(column="PKYMSTAFFID",property="pkYmStaffId"),
		@Result(column="REALNAME",property="realname"),
		@Result(column="PKYMORGID",property="orgname"),
		@Result(column="ROLEIDS",property="roleNames"),
		@Result(column="PASSWORD",property="password"),
	})
	@SelectProvider(method = "selectAllyinMaiStaffList",type = TblStaffMapperSqlConifg.class)
	List<TblStaff> selectAllyinMaiStaffList(Integer orgId) throws Exception;

	@Update("UPDATE TBL_STAFF SET PKYMSTAFFID =#{pkYmStaffId} WHERE STAFFID = #{staffid}")
	void updatePkYmStaffIdByStaffId(String pkYmStaffId, BigDecimal staffid);

//	@SelectProvider(method = "selectTblExternal",type = TblStaffMapperSqlConifg.class)
//	List<TblExternalExpert> selectTblExternal(PageInfo<TblExternalExpert> pageInfo, Find find);



//	void insertTblStaff(TblStaff tblStaff);


//	@Select("SELECT * FROM TBL_STAFF WHERE STAFFID = #{userid}")
//	TblStaff selectStaffId(BigDecimal userid);

	@Update("UPDATE TBL_STAFF SET PASSWORD =#{password} WHERE STAFFID = #{staffId}")
	void updateStaffPassWord(Integer staffId, String password) throws Exception;

	@SelectProvider(method = "selectStaffListByPageInfo", type = TblStaffMapperSqlConifg.class)
	List<TblStaff> selectStaffListByPageInfo(PageInfo<TblStaff> pageInfo,@Param("orgId")BigDecimal orgId, String realname)
			throws Exception;

	@SelectProvider(method = "selectStaffCountByPageInfo", type = TblStaffMapperSqlConifg.class)
	Integer selectStaffCountByPageInfo(BigDecimal orgId, String realname) throws Exception;

	//@Insert("INSERT INTO TBL_TRAININGSTAFF_ATT(STAFFID,ATTID) VALUES (${id},${attid})")
	//void insertAttInfoForStaff(@Param("id")BigDecimal id, @Param("attid")String attid) throws Exception;
	
	
	@InsertProvider(method = "insertAttInfoForStaff", type = TblStaffMapperSqlConifg.class)
	 void insertAttInfoForStaff(BigDecimal id,String attid) throws Exception;
	
	@InsertProvider(method = "insertAttInfoForTrain", type = TblStaffMapperSqlConifg.class)
	 void insertAttInfoForTrain(BigDecimal id,String attid) throws Exception;
	
	@Delete("DELETE FROM TBL_TRAININGSTAFF_ATT WHERE ATTID = #{attid}")
	void deleteStaffFileInfoByAttId(@Param("attid") BigDecimal attid) throws Exception;
	

	@Delete("DELETE FROM tbl_training_att WHERE ATTID = #{attid}")
	void deleteTrainFileInfoByAttId(@Param("attid") BigDecimal attid) throws Exception;
	
	@SelectProvider(method = "getAuditorInformationList", type = TblStaffMapperSqlConifg.class)
	 JSONObject getAuditorInformationList(String orgid) throws Exception;
	
	@Select("select * from tbl_staff where historycode=#{historycode}")
	List<TblStaff> getStffListByHistorycode(String historycode);
	
	
	@Update("update tbl_staff s set s.orgid=(select orgid from tbl_organization where historycode=s.historydepartmentid ) where s.datasource='zz' ")
	void updateTblStaffSyncOrg();

	
	@Update("update tbl_staff s set s.jobid=(select jobid from tbl_job where historycode=s.jobid ) where s.datasource='zz'")
	void updateTblStaffSyncJob();
	
	//==
    @SelectProvider(method="selectListOrgInId",type=TblStaffMapperSqlConifg.class)
	List<TblOrganization> selectListOrgInId(@Param("sysorgid") String sysorgid) throws Exception;
    
    //==
    @SelectProvider(method="selectListRoleInId",type=TblStaffMapperSqlConifg.class)
	List<TblRole> selectListRoleInId(@Param("roleids") String roleids) throws Exception;
}
